package com.daoImpl;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.LockMode;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.bean.ApplicablePart;
import com.bean.Craft;
import com.bean.CraftGrade;
import com.bean.CraftType;
import com.bean.ProcureProduct;
import com.bean.ProcureProductCraft;
import com.bean.ProductPart;
import com.dao.ProcureProductDao;

/**
 * A data access object (DAO) providing persistence and search support for
 * ProcureProduct entities. Transaction control of the save(), update() and
 * delete() operations can directly support Spring container-managed
 * transactions or they can be augmented to handle user-managed Spring
 * transactions. Each of these methods provides additional information for how
 * to configure it for the desired type of transaction control.
 * 
 * @see com.bean.ProcureProduct
 * @author MyEclipse Persistence Tools
 */
public class ProcureProductDaoImpl extends HibernateDaoSupport implements
		ProcureProductDao {
	private static final Logger log = LoggerFactory
			.getLogger(ProcureProductDaoImpl.class);
	// property constants
	public static final String PRODUCT_CODE = "productCode";
	public static final String SUPPELIER_COUNT = "suppelierCount";
	public static final String BLUEPRINT = "blueprint";
	public static final String PROC_COUNT = "procCount";
	public static final String STATUS = "status";
	public static final String SYS_USER = "sysUser";

	protected void initDao() {
		// do nothing
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.daoImpl.ProcureProductDao#save(com.bean.ProcureProduct)
	 */
	@Override
	public void save(ProcureProduct transientInstance) {
		log.debug("saving ProcureProduct instance");
		try {
			getHibernateTemplate().save(transientInstance);
			log.debug("save successful");
		} catch (RuntimeException re) {
			log.error("save failed", re);
			throw re;
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.daoImpl.ProcureProductDao#delete(com.bean.ProcureProduct)
	 */
	@Override
	public void delete(ProcureProduct persistentInstance) {
		log.debug("deleting ProcureProduct instance");
		try {
			getHibernateTemplate().delete(persistentInstance);
			log.debug("delete successful");
		} catch (RuntimeException re) {
			log.error("delete failed", re);
			throw re;
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.daoImpl.ProcureProductDao#findById(java.lang.Integer)
	 */
	@Override
	public ProcureProduct findById(java.lang.Integer id) {
		log.debug("getting ProcureProduct instance with id: " + id);
		try {
			ProcureProduct instance = (ProcureProduct) getHibernateTemplate()
					.get("com.bean.ProcureProduct", id);
			return instance;
		} catch (RuntimeException re) {
			log.error("get failed", re);
			throw re;
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.daoImpl.ProcureProductDao#findByExample(com.bean.ProcureProduct)
	 */
	@Override
	public List findByExample(ProcureProduct instance) {
		log.debug("finding ProcureProduct instance by example");
		try {
			List results = getHibernateTemplate().findByExample(instance);
			log.debug("find by example successful, result size: "
					+ results.size());
			return results;
		} catch (RuntimeException re) {
			log.error("find by example failed", re);
			throw re;
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.daoImpl.ProcureProductDao#findByProperty(java.lang.String,
	 * java.lang.Object)
	 */
	@Override
	public List findByProperty(String propertyName, Object value) {
		log.debug("finding ProcureProduct instance with property: "
				+ propertyName + ", value: " + value);
		try {
			String queryString = "from ProcureProduct as model where model."
					+ propertyName + "= ?";
			return getHibernateTemplate().find(queryString, value);
		} catch (RuntimeException re) {
			log.error("find by property name failed", re);
			throw re;
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.daoImpl.ProcureProductDao#findByProductCode(java.lang.Object)
	 */
	@Override
	public List findByProductCode(Object productCode) {
		return findByProperty(PRODUCT_CODE, productCode);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.daoImpl.ProcureProductDao#findBySuppelierCount(java.lang.Object)
	 */
	@Override
	public List findBySuppelierCount(Object suppelierCount) {
		return findByProperty(SUPPELIER_COUNT, suppelierCount);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.daoImpl.ProcureProductDao#findByBlueprint(java.lang.Object)
	 */
	@Override
	public List findByBlueprint(Object blueprint) {
		return findByProperty(BLUEPRINT, blueprint);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.daoImpl.ProcureProductDao#findByProcCount(java.lang.Object)
	 */
	@Override
	public List findByProcCount(Object procCount) {
		return findByProperty(PROC_COUNT, procCount);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.daoImpl.ProcureProductDao#findByStatus(java.lang.Object)
	 */
	@Override
	public List findByStatus(Object status) {
		return findByProperty(STATUS, status);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.daoImpl.ProcureProductDao#findBySysUser(java.lang.Object)
	 */
	@Override
	public List findBySysUser(Object sysUser) {
		return findByProperty(SYS_USER, sysUser);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.daoImpl.ProcureProductDao#findAll()
	 */
	@Override
	public List findAll() {
		log.debug("finding all ProcureProduct instances");
		try {
			String queryString = "from ProcureProduct";
			return getHibernateTemplate().find(queryString);
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.daoImpl.ProcureProductDao#merge(com.bean.ProcureProduct)
	 */
	@Override
	public ProcureProduct merge(ProcureProduct detachedInstance) {
		log.debug("merging ProcureProduct instance");
		try {
			ProcureProduct result = (ProcureProduct) getHibernateTemplate()
					.merge(detachedInstance);
			log.debug("merge successful");
			return result;
		} catch (RuntimeException re) {
			log.error("merge failed", re);
			throw re;
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.daoImpl.ProcureProductDao#attachDirty(com.bean.ProcureProduct)
	 */
	@Override
	public void attachDirty(ProcureProduct instance) {
		log.debug("attaching dirty ProcureProduct instance");
		try {
			getHibernateTemplate().saveOrUpdate(instance);
			log.debug("attach successful");
		} catch (RuntimeException re) {
			log.error("attach failed", re);
			throw re;
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.daoImpl.ProcureProductDao#attachClean(com.bean.ProcureProduct)
	 */
	@Override
	public void attachClean(ProcureProduct instance) {
		log.debug("attaching clean ProcureProduct instance");
		try {
			getHibernateTemplate().lock(instance, LockMode.NONE);
			log.debug("attach successful");
		} catch (RuntimeException re) {
			log.error("attach failed", re);
			throw re;
		}
	}

	public static ProcureProductDao getFromApplicationContext(
			ApplicationContext ctx) {
		return (ProcureProductDao) ctx.getBean("procureProductDao");
	}

	/**
	 * 采购需求 (non-Javadoc)
	 * 
	 * @see com.dao.ProcureProductDao#getFindProcurep(java.lang.String,
	 *      java.lang.String, java.lang.String, int, int, java.lang.String)
	 */
	@Override
	public List<ProcureProduct> getFindProcurep(String table, String pk,
			String sort, int PageNumber, int PageSize, String options) {
		Session session = getSession();
		SQLQuery query = null;

		StringBuffer sqlbuf = new StringBuffer(
				"execute [dbo].[sp_get_page_of_table2] ");
		sqlbuf.append("'" + table + "','" + pk + "','" + sort + "',"
				+ PageNumber + "," + PageSize + ",'*',\"" + options + "\"");

		query = session.createSQLQuery(sqlbuf.toString());

		List<Object[]> resultSet = query.list();
		List<ProcureProduct> list = new ArrayList<ProcureProduct>();
		for (Object[] r : resultSet) {

			ProcureProduct prop = new ProcureProduct();

			prop.setProcId((Integer) r[0]);
			prop.setProductCode((String) r[1]);
			prop.setSuppelierCount((Integer) r[2]);
			prop.setBlueprint((String) r[3]);
			prop.setProcCount((Integer) r[4]);
			prop.setStatus((Integer) r[5]);
			prop.setDeliveryDate((Timestamp) r[6]);
			prop.setStartTime((Timestamp) r[7]);
			prop.setEndTime((Timestamp) r[8]);
			prop.setCreateTime((Timestamp) r[9]);
			prop.setUpdateTime((Timestamp) r[10]);
			prop.setSysUser((String) r[11]);

			list.add(prop);
		}
		return list;
	}

	/**
	 * 获取明细列表
	 * 
	 * @returnProductPart
	 */
	public List<ProductPart> getProcurePList(Integer partId) {
		Session session = getSession();
		SQLQuery query = null;
		List<ProductPart> eMaterialList = new ArrayList<ProductPart>();
		String sql = "select prodp.part_id,prodp.part_code,prodp.product_code,prodp.photo,prodp.apl_id,prodp.size_range,prodp.memo ,part_name" +
				" from audit.product_part prodp "+
				" inner join audit.procure_product_craft procp " +
				" on prodp.part_id = procp.part_id " +
				" INNER JOIN audit.applicable_part ap " +
				" on ap.apl_id = prodp.apl_id " +
				" WHERE procp.proc_id=:partId";
		query = session.createSQLQuery(sql);
		query.setInteger("partId", partId);
		List<Object[]> resultSet = query.list();

		List<ProductPart> list = new ArrayList<ProductPart>();
		for (Object[] r : resultSet) {
			ProductPart propart = new ProductPart();
			propart.setPartId((Integer)r[0]);
			propart.setPartCode((String)r[1]);
			propart.setProductCode((String)r[2]);
			propart.setPhoto((String)r[3]);
			ApplicablePart ap=new ApplicablePart();
			ap.setAplId((Integer)r[4]);
			propart.setSizeRange((String)r[5]);
			propart.setMemo((String)r[6]);
			ap.setPartName((String)r[7]);
			propart.setApplicablePart(ap);
			list.add(propart);

		}
		return list;
	}

	@Override
	public List<Craft> getCraftList(Integer craftId) {
		Session session = getSession();
		SQLQuery query = null;
		List<Craft> eMaterialList = new ArrayList<Craft>();
		String sql = "SELECT c.craft_id,c.craft_code,c.craft_name,t.craft_type_id,type_name,g.grade_id,grade_name,c.memo"
				+ " FROM audit.craft_part cp"
				+ " inner join audit.craft c  on c.craft_id = cp.craft_id "
				+ " INNER JOIN audit.craft_grade g ON c.grade_id = g.grade_id"
				+ " INNER JOIN audit.craft_type t ON c.craft_type_id = t.craft_type_id"
				+ " WHERE cp.part_id=:craftId";
		query = session.createSQLQuery(sql);
		query.setInteger("craftId", craftId);
		List<Object[]> resultSet = query.list();

		List<Craft> list = new ArrayList<Craft>();
		for (Object[] r : resultSet) {
			Craft cra = new Craft();
			cra.setCraftId((Integer)r[0]);
			cra.setCraftCode((String)r[1]);
			cra.setCraftName((String)r[2]);
			CraftType crt=new CraftType();
			crt.setCraftTypeId((Integer)r[3]);
			crt.setTypeName((String)r[4]);
			cra.setCraftType(crt);
			CraftGrade cg= new CraftGrade();
			cg.setGradeId((Integer)r[5]);
			cg.setGradeName((String)r[6]);
			cra.setCraftGrade(cg);
			cra.setMemo((String)r[7]);
			list.add(cra);
		}
		return list;
	}
	
	
	
	/**
	 * 添加采购需求
	 */
	public void saveProductPart(ProcureProduct procureProduct , StringBuffer testDetailStrs){
		Session session = getSession();  
		SQLQuery query=null;

		StringBuffer sqlbuf = new StringBuffer("execute [audit].[sp_add_procure_product] ");
		sqlbuf.append("@product_code='"+procureProduct.getProductCode());
		sqlbuf.append("',@suppelier_count = "+procureProduct.getSuppelierCount()+"");
		sqlbuf.append(",@blueprint = '"+procureProduct.getBlueprint()+"'");
		sqlbuf.append(",@status = "+procureProduct.getStatus()+"");
		sqlbuf.append(",@proc_count = "+procureProduct.getProcCount()+"");
		sqlbuf.append(",@delivery_date = '"+procureProduct.getDeliveryDate()+"'");
		sqlbuf.append(",@start_time = '"+procureProduct.getStartTime()+"'");
		sqlbuf.append(",@end_time = '"+procureProduct.getEndTime()+"'");
		sqlbuf.append(",@create_time = '"+procureProduct.getCreateTime()+"'");
		sqlbuf.append(",@update_time = '"+procureProduct.getUpdateTime()+"'");
		sqlbuf.append(",@sys_user = '"+procureProduct.getSysUser()+"'");
		sqlbuf.append(",@details = '"+testDetailStrs+"'");
		if (procureProduct.getProcId()!=null) {
			sqlbuf.append(",@proc_id = "+procureProduct.getProcId()+"");
		} 
		query=session.createSQLQuery(sqlbuf.toString());
		
		query.executeUpdate();

	}
	
	/**
	 * 删除采购需求
	 */
	public void deleteProcureProduct(Integer procId){
		Session session = getSession();  
		SQLQuery query=null;
		
		StringBuffer sqlbuf = new StringBuffer("execute [audit].[sp_delete_procure_product] ");		
		sqlbuf.append("@proc_id="+procId);
		
		query=session.createSQLQuery(sqlbuf.toString());
		
		query.executeUpdate();
	}
	
	
	/**
	 * 发布采购需求
	 */
	public void saveProcureInquiry(StringBuffer sql){
		Session session = getSession();  
		SQLQuery query=null;

		StringBuffer sqlbuf = new StringBuffer("insert into [audit].procure_inquiry(proc_id,suid,send_time,end_time,remark) VALUES");
		
		sqlbuf.append(sql);
		
		query=session.createSQLQuery(sqlbuf.toString());
		
		query.executeUpdate();
	}
	
}